1. Field of the Invention
This invention relates in general to database management systems performed by computers, and in particular to an optimized remote computer system database debugging, monitoring and managing technique useable in a client-server computing environment.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data. RDBMS software using a Structured Query Language (SQL) interface is well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Organization (ANSI) and the International Standards Organization (ISO).
Recently, Persistent SQL Modules (PSM) language became a standard language for coding SQL-based logic, such as stored procedures, user defined functions (UDFs) and triggers. PSM is very similar to Microsoft SQL Server's TSQL and Oracle PL/SQL, which are proprietary technologies and thus not standard. PSM allows for the development of DB2 stored procedures using the SQL language.
As with any development language, there exists a need to provide an efficient mechanism for debugging routines coded in SQL language, and especially for debugging stored procedures with SQL instructions. An efficient debugger currently exists for IBM DB2's Java and C/C++ stored procedures, which are currently debugged using IBM's Visual Age distributed debugger. There is a clear need to provide a similar cross-platform debugging support for SQL stored procedures. However, an efficient debugger for SQL stored procedures does not exist yet, and it has to be designed specifically for debugging SQL instructions written in PSM, because current DB2 implementation of PSM requires pre-compilation of PSM source code into C language code.
Currently, the support for DB2's SQL language stored procedures is achieved through a three-step compilation process. Step one compiles a PSM SQL stored procedure to an embedded SQL C file (file-name.sqc) using the DB2 PSM compiler. Step two compiles the file-name.sqc file to a standard C file using the DB2 SQL precompiler. Step three compiles the C file to generate the required execution library using any of the supported C language compilers. Therefore, the current implementation of PSM by DB2 is based on precompiling the PSM source into C language code, although in the future PSM may become an interpreted SQL language.
The basic requirement of any debugger is the ability to step through the original source code, view and modify variables, manage breakpoints, and capture execution exceptions. The C/C++ debugger is not suited to handle the debugging of SQL instructions, because the original PSM source code has to undergo three compilation steps, mentioned above, where the SQL language commands are translated into C language calls, and the SQL variables are translated into C language structures. Further, each SQL statement translates into multiple C language calls across multiple lines of code. Moreover, SQL language exception handling is very different from C/C++ exception handling, for which C/C++ debuggers are designed.
Attempts have been made to utilize the IBM's Visual Age C/C++ distributed debugger to debug SQL stored procedures. This is accomplished through some complex line macro mapping to the original SQL source file. However, there are a number of problems with this implementation that makes it very unusable. For example, C/C++ debugger is not suited for SQL language debugging because each SQL statement translates into multiple C statements, which may require multiple debugger commands to step over each line of SQL code. Next, SQL variable types translate into C structures foreign to SQL programmers. Moreover, SQL variable names are mangled while they go through the three compilation steps, and thus become hard to read by a SQL programmer. Furthermore, SQL variable values cannot be formatted according to SQL type value formatting, since they are treated as plain C/C++ types. Besides, SQL exception handling differs from C/C++ exception handling, making it difficult to detect by C/C++ debuggers.
Further, C/C++ debuggers work through standard debugger process attachment. This is an unacceptable risk to be taken by database administrators, as it not only assumes control of the SQL routine being debugged, but also the entire database engine, affecting all of its users. Moreover, C/C++ debuggers are not applicable to interpreted languages.
While there have been various techniques developed for optimizing the remote systems debugging and management functions, there is still a need in the art for further optimization techniques involving remote systems debugging operations. Therefore, it would be advantageous to provide a computer method that efficiently debugs and controls the target computer site, is easy to implement and maintain, and decreases the use of communication resources between processors.